/**
 * 
 */
package edu.mtu.citizenscience.ethnography.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author Nicholas Blecha
 *
 */
class EthnographySQLiteOpenHelper extends SQLiteOpenHelper {

	private static final String LOG_TAG = "EthnographySQLiteOpenHelper";

	// Table/column name constants
	/**
	 * String constant describing the name of the "Interviews" table
	 */
	static final String TABLE_INTERVIEWS = "interviews";
	/**
	 * String constant describing the name of the "ID" column
	 */
	static final String COLUMN_ID = "_id";
	/**
	 * String constant describing the name of the "Subject First Name"
	 * column in the "Interviews" table
	 */
	static final String COLUMN_SUB_FIRST_NAME = "sub_first_name";
	/**
	 * String constant describing the name of the "Subject Last Name"
	 * column in the "Interviews" table
	 */
	static final String COLUMN_SUB_LAST_NAME = "sub_last_name";
	/**
	 * String constant describing the name of the "Interviewer first name"
	 * column in the "Interviews" table
	 */
	static final String COLUMN_INTER_FIRST_NAME = "inter_first_name";
	/**
	 * String constant describing the name of the "Interviewer last name"
	 * column in the "Interviews" table
	 */
	static final String COLUMN_INTER_LAST_NAME = "inter_last_name";
	/**
	 * String constant describing the name of the "Location name" column
	 * in the "Interviews" table 
	 */
	static final String COLUMN_LOCATION_NAME = "location_name";

	static final String COLUMN_STORY_TITLE = "title";

	// Date/time based columns
	static final String COLUMN_YEAR = "year";
	static final String COLUMN_MONTH = "month";
	static final String COLUMN_DAY = "day";
	// This is a 24-hour field
	static final String COLUMN_HOUR = "hour";
	static final String COLUMN_MINUTE = "minute";
	static final String COLUMN_SECOND = "second";

	/**
	 * String constant describing the name of the "Notes" column in the
	 * "Interviews" table
	 */
	static final String COLUMN_NOTES = "notes";
	/**
	 * String constant describing the name of the "Media Path" column in the
	 * "Interviews" table
	 */
	static final String COLUMN_MEDIA_PATH = "media_path";
	/**
	 * String constant describing the name of the "Uploaded" column
	 * in the "Interviews" table
	 */
	static final String COLUMN_UPLOADED = "uploaded";
	static final String COLUMN_SERVER_KEY = "server_key";

	static final String TABLE_AUDIO = "audio";

	static final String TABLE_PHOTO = "photos";

	static final String COLUMN_FILE_NAME = "file_name";

	static final String COLUMN_INTERVIEW_ID = "interview_id";

	// Location columns
	static final String COLUMN_START_LATITUDE = "start_latitude";
	static final String COLUMN_START_LONGITUDE = "start_longitude";
	static final String COLUMN_END_LATITUDE = "end_latitude";
	static final String COLUMN_END_LONGITUDE = "end_longitude";

	// Filename/version constants
	private static final String DATABASE_NAME = "interviews.db";
	private static final int DATABASE_VERSION = 3;

	// Database creation string
	private static final String INTERVIEWS_CREATE = "create table " + 
			TABLE_INTERVIEWS + "(" +
			COLUMN_ID + " integer primary key autoincrement, " +
			COLUMN_STORY_TITLE + " text not null, " +
			COLUMN_SUB_FIRST_NAME + " text not null, " +
			COLUMN_SUB_LAST_NAME + " text not null, " +
			COLUMN_INTER_FIRST_NAME + " text not null, " +
			COLUMN_INTER_LAST_NAME + " text not null, " +
			COLUMN_LOCATION_NAME + " text not null, " +
			COLUMN_YEAR + " integer not null, " +
			COLUMN_MONTH + " integer not null, " +
			COLUMN_DAY + " integer not null, " +
			COLUMN_NOTES + " text not null, " +
			COLUMN_MEDIA_PATH + " text not null, " +
			COLUMN_UPLOADED + " integer not null," +
			COLUMN_SERVER_KEY + " integer);";

	private static final String AUDIO_CREATE = "create table " +
			TABLE_AUDIO + "(" +
			COLUMN_FILE_NAME + " text, " +
			COLUMN_INTERVIEW_ID + " integer, "+
			COLUMN_START_LATITUDE + " real, " +
			COLUMN_START_LONGITUDE + " real, " +
			COLUMN_END_LATITUDE + " real, " +
			COLUMN_END_LONGITUDE + " real, " +
			COLUMN_YEAR + " integer not null, " +
			COLUMN_MONTH + " integer not null, " +
			COLUMN_DAY + " integer not null, " +
			COLUMN_HOUR + " integer not null, " +
			COLUMN_MINUTE + " integer not null, " +
			COLUMN_SECOND + " integer not null, " +
			COLUMN_UPLOADED + " integer not null, " +
			" primary key(" + COLUMN_FILE_NAME + ", " + COLUMN_INTERVIEW_ID + ")" +
			" foreign key("+COLUMN_INTERVIEW_ID+")" +
			" references " + TABLE_INTERVIEWS + "(" + COLUMN_ID + ")" +
			" on delete restrict on update cascade);";

	private static final String PHOTO_CREATE = "create table " +
			TABLE_PHOTO + "(" +
			COLUMN_FILE_NAME + " text, " +
			COLUMN_INTERVIEW_ID + " integer, "+
			COLUMN_START_LATITUDE + " real, " +
			COLUMN_START_LONGITUDE + " real, " +
			COLUMN_YEAR + " integer not null, " +
			COLUMN_MONTH + " integer not null, " +
			COLUMN_DAY + " integer not null, " +
			COLUMN_HOUR + " integer not null, " +
			COLUMN_MINUTE + " integer not null, " +
			COLUMN_SECOND + " integer not null, " +
			COLUMN_UPLOADED + " integer not null, " +
			" primary key(" + COLUMN_FILE_NAME + ", " + COLUMN_INTERVIEW_ID + ")" +
			" foreign key("+COLUMN_INTERVIEW_ID+")" +
			" references " + TABLE_INTERVIEWS + "(" + COLUMN_ID + ")" +
			" on delete restrict on update cascade);";

	EthnographySQLiteOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(LOG_TAG, "Creating "+TABLE_INTERVIEWS+" table");
		try {
			db.beginTransaction();
			db.execSQL(INTERVIEWS_CREATE);
			db.execSQL(AUDIO_CREATE);
			db.execSQL(PHOTO_CREATE);
			db.setTransactionSuccessful();
		} catch (SQLException e) {
			Log.e(LOG_TAG, "SQL returned an unexpected exception", e);
			throw e;
		} finally {
			db.endTransaction();
		}
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(LOG_TAG, String.format("Upgrading %s table (v.%d to v.%d)",
				TABLE_INTERVIEWS, oldVersion, newVersion));

		if (oldVersion < 2 && newVersion >= 2) {
			try {
				db.beginTransaction();

				// Add the "Story Title" column to the interviews table
				String oldInterviewsTable = TABLE_INTERVIEWS + "_old";
				db.execSQL("ALTER TABLE " + TABLE_INTERVIEWS + " RENAME TO " + oldInterviewsTable + ";");
				db.execSQL(INTERVIEWS_CREATE);

				String[] oldInterviewColumns = { COLUMN_ID,
						COLUMN_SUB_FIRST_NAME,
						COLUMN_SUB_LAST_NAME,
						COLUMN_INTER_FIRST_NAME,
						COLUMN_INTER_LAST_NAME,
						COLUMN_LOCATION_NAME,
						COLUMN_YEAR,
						COLUMN_MONTH,
						COLUMN_DAY,
						COLUMN_NOTES,
						COLUMN_MEDIA_PATH,
						COLUMN_UPLOADED};

				Cursor cursor = db.query(oldInterviewsTable, oldInterviewColumns, null, null, null, null, null);

				for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
					ContentValues values = new ContentValues();
					values.put(COLUMN_ID, cursor.getInt(0));
					values.put(COLUMN_STORY_TITLE, "");
					values.put(COLUMN_SUB_FIRST_NAME, cursor.getString(1));
					values.put(COLUMN_SUB_LAST_NAME, cursor.getString(2));
					values.put(COLUMN_INTER_FIRST_NAME, cursor.getString(3));
					values.put(COLUMN_INTER_LAST_NAME, cursor.getString(4));
					values.put(COLUMN_LOCATION_NAME, cursor.getString(5));
					values.put(COLUMN_YEAR, cursor.getInt(6));
					values.put(COLUMN_MONTH, cursor.getInt(7));
					values.put(COLUMN_DAY, cursor.getInt(8));
					values.put(COLUMN_NOTES, cursor.getString(9));
					values.put(COLUMN_MEDIA_PATH, cursor.getString(10));
					values.put(COLUMN_UPLOADED, cursor.getInt(11));
					db.insert(TABLE_INTERVIEWS, null, values);
				}
				
				cursor.close();

				db.execSQL("DROP TABLE " + oldInterviewsTable + ";");

				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}

		if (oldVersion < 3 && newVersion >= 3) {
			try {
				db.beginTransaction();
				// Add the "server_key" column to the interviews table
				String oldInterviewsTable = TABLE_INTERVIEWS + "_old";
				db.execSQL("ALTER TABLE " + TABLE_INTERVIEWS + " RENAME TO " + oldInterviewsTable + ";");
				db.execSQL(INTERVIEWS_CREATE);

				String[] oldInterviewColumns = { COLUMN_ID,
						COLUMN_STORY_TITLE,
						COLUMN_SUB_FIRST_NAME,
						COLUMN_SUB_LAST_NAME,
						COLUMN_INTER_FIRST_NAME,
						COLUMN_INTER_LAST_NAME,
						COLUMN_LOCATION_NAME,
						COLUMN_YEAR,
						COLUMN_MONTH,
						COLUMN_DAY,
						COLUMN_NOTES,
						COLUMN_MEDIA_PATH,
						COLUMN_UPLOADED};

				Cursor cursor = db.query(oldInterviewsTable, oldInterviewColumns, null, null, null, null, null);

				for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
					ContentValues values = new ContentValues();
					values.put(COLUMN_ID, cursor.getInt(0));
					values.put(COLUMN_STORY_TITLE, cursor.getString(1));
					values.put(COLUMN_SUB_FIRST_NAME, cursor.getString(2));
					values.put(COLUMN_SUB_LAST_NAME, cursor.getString(3));
					values.put(COLUMN_INTER_FIRST_NAME, cursor.getString(4));
					values.put(COLUMN_INTER_LAST_NAME, cursor.getString(5));
					values.put(COLUMN_LOCATION_NAME, cursor.getString(6));
					values.put(COLUMN_YEAR, cursor.getInt(7));
					values.put(COLUMN_MONTH, cursor.getInt(8));
					values.put(COLUMN_DAY, cursor.getInt(9));
					values.put(COLUMN_NOTES, cursor.getString(10));
					values.put(COLUMN_MEDIA_PATH, cursor.getString(11));
					values.put(COLUMN_UPLOADED, cursor.getInt(12));
					values.put(COLUMN_SERVER_KEY, 0);
					db.insert(TABLE_INTERVIEWS, null, values);
				}

				cursor.close();
				
				db.execSQL("DROP TABLE " + oldInterviewsTable + ";");

				// Add the "uploaded" column to the audio table
				String oldAudioTable = TABLE_AUDIO + "_old";
				
				db.execSQL("ALTER TABLE " + TABLE_AUDIO + " RENAME TO " + oldAudioTable + ";");
				db.execSQL(AUDIO_CREATE);
				
				String[] oldAudioColumns = {COLUMN_FILE_NAME,
						COLUMN_INTERVIEW_ID,
						COLUMN_START_LATITUDE,
						COLUMN_START_LONGITUDE,
						COLUMN_END_LATITUDE,
						COLUMN_END_LONGITUDE,
						COLUMN_YEAR,
						COLUMN_MONTH,
						COLUMN_DAY,
						COLUMN_HOUR,
						COLUMN_MINUTE,
						COLUMN_SECOND};
				
				cursor = db.query(oldAudioTable, oldAudioColumns, null, null, null, null, null);
				
				for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
					ContentValues values = new ContentValues();
					values.put(COLUMN_FILE_NAME, cursor.getString(0));
					values.put(COLUMN_INTERVIEW_ID, cursor.getInt(1));
					values.put(COLUMN_START_LATITUDE, cursor.getDouble(2));
					values.put(COLUMN_START_LONGITUDE, cursor.getDouble(3));
					values.put(COLUMN_END_LATITUDE, cursor.getDouble(4));
					values.put(COLUMN_END_LONGITUDE, cursor.getDouble(5));
					values.put(COLUMN_YEAR, cursor.getInt(6));
					values.put(COLUMN_MONTH, cursor.getInt(7));
					values.put(COLUMN_DAY, cursor.getInt(8));
					values.put(COLUMN_HOUR, cursor.getInt(9));
					values.put(COLUMN_MINUTE, cursor.getInt(10));
					values.put(COLUMN_SECOND, cursor.getInt(11));
					values.put(COLUMN_UPLOADED, 0);
					db.insert(TABLE_AUDIO, null, values);
				}
				
				cursor.close();
				
				db.execSQL("DROP TABLE " + oldAudioTable + ";");
				
				// Add the "uploaded" column to the photo table
				String oldPhotoTable = TABLE_PHOTO + "_old";
				
				db.execSQL("ALTER TABLE " + TABLE_PHOTO + " RENAME TO " + oldPhotoTable + ";");
				db.execSQL(PHOTO_CREATE);
				
				String[] oldPhotoColumns = {COLUMN_FILE_NAME,
						COLUMN_INTERVIEW_ID,
						COLUMN_START_LATITUDE,
						COLUMN_START_LONGITUDE,
						COLUMN_YEAR,
						COLUMN_MONTH,
						COLUMN_DAY,
						COLUMN_HOUR,
						COLUMN_MINUTE,
						COLUMN_SECOND};
				
				cursor = db.query(oldPhotoTable, oldPhotoColumns, null, null, null, null, null);
				
				for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
					ContentValues values = new ContentValues();
					values.put(COLUMN_FILE_NAME, cursor.getString(0));
					values.put(COLUMN_INTERVIEW_ID, cursor.getInt(1));
					values.put(COLUMN_START_LATITUDE, cursor.getDouble(2));
					values.put(COLUMN_START_LONGITUDE, cursor.getDouble(3));
					values.put(COLUMN_YEAR, cursor.getInt(4));
					values.put(COLUMN_MONTH, cursor.getInt(5));
					values.put(COLUMN_DAY, cursor.getInt(6));
					values.put(COLUMN_HOUR, cursor.getInt(7));
					values.put(COLUMN_MINUTE, cursor.getInt(8));
					values.put(COLUMN_SECOND, cursor.getInt(9));
					values.put(COLUMN_UPLOADED, 0);
					db.insert(TABLE_PHOTO, null, values);
				}
				
				cursor.close();
				
				db.execSQL("DROP TABLE " + oldPhotoTable + ";");
				
				db.setTransactionSuccessful();

			} finally {
				db.endTransaction();
			}
		}
	}

}
